package com.city.repository.report;

import com.city.format.CtReportFormatSql;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CtReportFormatSqlRepository extends JpaRepository<CtReportFormatSql,Integer> {


    @Query(value = "SELECT r.id id , r.task_id task_id ,r.title title ,r.create_time create_time_name ,r.schedule schedule, r.is_do_ban is_do_ban ," +
            "p3.name agreee_man ,b.name work_branch ," +
            "e1.name type_big_name,e2.name type_small_name ," +
            "p1.name report_name ,p2.name check_man_name ," +
            "t.name category_name " +
            "from ct_report as r " +
            "left join ct_event as e1 on r.type_big = e1.id " +
            "left join ct_event as e2 on r.type_small = e2.id " +
            "left join ct_user as p1 on r.report_id = p1.id " +
            "left join ct_user as p2 on r.check_man_id = p2.id " +
            "LEFT join ct_user as p3 on r.handle_man_id = p3.id " +
            "LEFT join ct_branch as b on r.branch_id = b.id " +
            "left join ct_category as t on r.category_id = t.id  where r.schedule = ?2 and r.branch_id = ?1 limit ?3 ,?4",nativeQuery = true)
    List<CtReportFormatSql> findTasks(Integer branchId,Integer schedule,long page,long limit);

    @Query(value = "SELECT count(*) from ct_report where schedule = ?1 and branch_id = ?2 ",nativeQuery = true)
    long countTasks(Integer schedule,Integer branchId);


    @Query(value = "SELECT r.id id , r.task_id task_id ,r.title title ,r.create_time create_time_name ,r.schedule schedule, r.is_do_ban is_do_ban ," +
            "p3.name agreee_man ,b.name work_branch ," +
            "e1.name type_big_name,e2.name type_small_name ," +
            "p1.name report_name ,p2.name check_man_name ," +
            "t.name category_name " +
            "from ct_report as r " +
            "left join ct_event as e1 on r.type_big = e1.id " +
            "left join ct_event as e2 on r.type_small = e2.id " +
            "left join ct_user as p1 on r.report_id = p1.id " +
            "LEFT join ct_user as p3 on r.handle_man_id = p3.id " +
            "LEFT join ct_branch as b on r.branch_id = b.id " +
            "left join ct_user as p2 on r.check_man_id = p2.id " +
            "left join ct_category as t on r.category_id = t.id  where r.schedule = ?2 and r.check_man_id = ?1 order by r.update_time limit ?3,?4",nativeQuery = true)
    List<CtReportFormatSql> findMyTasks(Integer branchId,Integer schedule,long page,long limit);

    @Query(value = "SELECT count(*) from ct_report where schedule = ?1 and check_man_id = ?2 ",nativeQuery = true)
    long countMyTasks(Integer schedule,Integer id);


    @Query(value = "SELECT r.id id , r.task_id task_id ,r.title title ,r.create_time create_time_name ,r.schedule schedule, r.is_do_ban is_do_ban , " +
            "p3.name agreee_man ,b.name work_branch ," +
            "e1.name type_big_name,e2.name type_small_name ," +
            "p1.name report_name ,p2.name check_man_name ," +
            "t.name category_name " +
            "from ct_report as r " +
            "left join ct_event as e1 on r.type_big = e1.id " +
            "left join ct_event as e2 on r.type_small = e2.id " +
            "left join ct_user as p1 on r.report_id = p1.id " +
            "LEFT join ct_user as p3 on r.handle_man_id = p3.id " +
            "LEFT join ct_branch as b on r.branch_id = b.id " +
            "left join ct_user as p2 on r.check_man_id = p2.id " +
            "left join ct_category as t on r.category_id = t.id  where r.schedule > ?1 and r.schedule < ?2 order by r.update_time  ",nativeQuery = true)
    List<CtReportFormatSql> findDiary(Integer left ,Integer right);



    @Query(value = "SELECT r.id id , r.task_id task_id ,r.title title ,r.create_time create_time_name ,r.schedule schedule, r.is_do_ban is_do_ban , " +
            "p3.name agreee_man ,b.name work_branch ," +
            "e1.name type_big_name,e2.name type_small_name ," +
            "p1.name report_name ,p2.name check_man_name ," +
            "t.name category_name " +
            "from ct_report as r " +
            "left join ct_event as e1 on r.type_big = e1.id " +
            "left join ct_event as e2 on r.type_small = e2.id " +
            "left join ct_user as p1 on r.report_id = p1.id " +
            "LEFT join ct_user as p3 on r.handle_man_id = p3.id " +
            "LEFT join ct_branch as b on r.branch_id = b.id " +
            "left join ct_user as p2 on r.check_man_id = p2.id " +
            "left join ct_category as t on r.category_id = t.id  where r.schedule > ?1 and r.schedule < ?2 order by r.update_time desc limit ?3,?4  ",nativeQuery = true)
    List<CtReportFormatSql> findDiaryByPage(Integer left ,Integer right,long page,long limit);


    @Query(value = "SELECT r.id id , r.task_id task_id ,r.title title ,r.create_time create_time_name ,r.schedule schedule, r.is_do_ban is_do_ban , " +
            "p3.name agreee_man ,b.name work_branch ," +
            "e1.name type_big_name,e2.name type_small_name ," +
            "p1.name report_name ,p2.name check_man_name ," +
            "t.name category_name " +
            "from ct_report as r " +
            "left join ct_event as e1 on r.type_big = e1.id " +
            "left join ct_event as e2 on r.type_small = e2.id " +
            "left join ct_user as p1 on r.report_id = p1.id " +
            "LEFT join ct_user as p3 on r.handle_man_id = p3.id " +
            "LEFT join ct_branch as b on r.branch_id = b.id " +
            "left join ct_user as p2 on r.check_man_id = p2.id " +
            "left join ct_category as t on r.category_id = t.id  where r.schedule > ?1 and r.schedule < ?2 and r.branch_id = ?3 order by r.update_time desc",nativeQuery = true)
    List<CtReportFormatSql> findReport(Integer left ,Integer right,Integer branchId);


    @Query(value = "SELECT count(*) from ct_report where schedule > ?1 and schedule < ?2",nativeQuery = true)
    long countReport(Integer left,Integer right);

    @Query(value = "SELECT count(*) from ct_report where schedule > ?1 and schedule < ?2 and branch_id = ?3",nativeQuery = true)
    long countReportByLeft(Integer left,Integer right,Integer branchId);

    @Query(value = "SELECT r.id id , r.task_id task_id ,r.title title ,r.create_time create_time_name ,r.schedule schedule, r.is_do_ban is_do_ban , " +
            "p3.name agreee_man ,b.name work_branch ," +
            "e1.name type_big_name,e2.name type_small_name ," +
            "p1.name report_name ,p2.name check_man_name ," +
            "t.name category_name " +
            "from ct_report as r " +
            "left join ct_event as e1 on r.type_big = e1.id " +
            "left join ct_event as e2 on r.type_small = e2.id " +
            "left join ct_user as p1 on r.report_id = p1.id " +
            "LEFT join ct_user as p3 on r.handle_man_id = p3.id " +
            "LEFT join ct_branch as b on r.branch_id = b.id " +
            "left join ct_user as p2 on r.check_man_id = p2.id " +
            "left join ct_category as t on r.category_id = t.id  where r.schedule > ?1 and r.schedule < ?2 and r.branch_id = ?3 order by r.update_time desc limit ?4, ?5",nativeQuery = true)
    List<CtReportFormatSql> findDiaryByBranch(Integer left ,Integer right,Integer branchId,long page,long limit);

    /**
     * @author  王宇
     * @date 2018/9/18
     * @description  回撤案件
     */
    @Query(value = "SELECT count(*) from ct_report where iscaseback = 1",nativeQuery = true)
    long countCaseBack();

    @Query(value = "SELECT r.id id , r.task_id task_id ,r.title title ,r.create_time create_time_name ,r.schedule schedule, r.is_do_ban is_do_ban , " +
            "p3.name agreee_man ,b.name work_branch ," +
            "e1.name type_big_name,e2.name type_small_name ," +
            "p1.name report_name ,p2.name check_man_name ," +
            "t.name category_name " +
            "from ct_report as r " +
            "left join ct_event as e1 on r.type_big = e1.id " +
            "left join ct_event as e2 on r.type_small = e2.id " +
            "left join ct_user as p1 on r.report_id = p1.id " +
            "LEFT join ct_user as p3 on r.handle_man_id = p3.id " +
            "LEFT join ct_branch as b on r.branch_id = b.id " +
            "left join ct_user as p2 on r.check_man_id = p2.id " +
            "left join ct_category as t on r.category_id = t.id  where r.iscaseback=1 order by r.update_time desc limit ?1, ?2",nativeQuery = true)
    List<CtReportFormatSql> findCaseBack(long page,long limit);

}
